﻿/* =========================================================
Author:		Andrew Butenko
Create date:	2012-05-01.
Description:	generates LOV entity view with instead trigger
	
Example:
	exec [lov].[usp_GenerateEntities] @EntityNamePattern='%'
============================================================ */
CREATE PROCEDURE [lov].[usp_GenerateEntities]
(
	@EntityNamePattern  varchar(255)=null,
	@EntityId int=null,
	@BitMask tinyint=1 -- 1-print|2-execute|3-print and execute
)
WITH EXECUTE AS OWNER
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @FieldDef TABLE
		(
			FieldId int NOT NULL,
			FieldName nvarchar(128) NOT NULL,
			FieldType nvarchar(128) NOT NULL,
			--FieldLength int NOT NULL,
			--FieldPrecision tinyint NOT NULL,
			--FieldScale tinyint NOT NULL,
			--FieldIsAnsiPadded bit null
			FieldOrder tinyint NOT NULL,
			FieldIsNullable bit null,
			FieldIsPrimaryKey bit null,
			FieldIsIdentity bit null
		)
	DECLARE @EntityName nvarchar(128), @EntityId2 int, @EntityDescription varchar(255), @UseRowIdAsPrimaryKey bit = 0
		, @FieldList nvarchar(max), @FieldDefinitionNoIDList nvarchar(max), @VariableAssignmentNoIDList nvarchar(max), @ColumnPairList nvarchar(max)
		, @PKColumnPairList nvarchar(max), @FieldDefinitionList nvarchar(max), @FieldIdList nvarchar(max)
		, @MergeValuesList nvarchar(max), @VariableDeclarationList nvarchar(max), @LeftOuterJoinValueList nvarchar(max), @START_POS int=1,@END_POS int=1
		, @Schema nvarchar(100), @SQL varchar(max)='', @SQL2 nvarchar(max)=''
	DECLARE c_Entities CURSOR LOCAL FORWARD_ONLY STATIC  READ_ONLY  FOR
		SELECT DISTINCT e.Id as EntityId, e.[Schema], e.Name  AS [EntityName], e.[Description] as EntityDescription
		FROM lov.Entity e 
		INNER JOIN lov.Field f on e.Id = f.EntityId
		WHERE (e.Name like @EntityNamePattern OR e.Id = @EntityId)
			AND f.Name !='RowId' -- this name is reserved by lov solution
		ORDER BY e.Name
		OPEN c_Entities 
		FETCH NEXT FROM c_Entities INTO @EntityId2, @Schema, @EntityName, @EntityDescription
		WHILE (@@FETCH_STATUS<>-1)
		BEGIN
			IF (@@FETCH_STATUS<>-2)
			BEGIN
				/* Assign initial variable values */
				DELETE FROM @FieldDef
				SELECT @FieldList =',', @FieldDefinitionNoIDList =',', @VariableAssignmentNoIDList=','
					, @ColumnPairList = 'and', @PKColumnPairList = 'and', @FieldDefinitionList = ','
					,@MergeValuesList ='', @VariableDeclarationList=',', @FieldIdList=',', @LeftOuterJoinValueList=''

				INSERT INTO @FieldDef (FieldId,FieldName,FieldType,FieldOrder,FieldIsNullable,FieldIsPrimaryKey,FieldIsIdentity)
				SELECT f.Id, f.Name as FieldName
						,f.DataType as FieldType
						,isnull(f.SortOrder,convert(tinyint,ROW_NUMBER()OVER(ORDER BY f.Name))) as FieldOrder
						,isnull(f.Nullable,0) as FieldIsNullable
						,isnull(PrimaryKey,0) as FieldIsPrimaryKey
						,0 as FieldIsIdentity
				FROM lov.Field f
				WHERE f.EntityID = @EntityId2
					AND f.Name !='RowId' -- this name is reserved by lov solution
				ORDER BY  f.PrimaryKey, f.Name

				SET @UseRowIdAsPrimaryKey = 0
				SELECT TOP 1 @UseRowIdAsPrimaryKey = 1
				FROM lov.Field f
				WHERE f.EntityID = @EntityId2
					AND f.Name ='RowId' -- this name is reserved by lov solution

		;WITH TypeDafault AS
		(SELECT DISTINCT FieldType
			,(case --select * from sys.types where is_user_defined=0 order by name
						when FieldType = 'bigint' then '0'
						when FieldType = 'bit' then '0'
						when FieldType like 'decimal%' then '0'
						when FieldType = 'float' then '0'
						when FieldType = 'int' then '0'
						when FieldType = 'money' then '0'
						when FieldType like 'numeric%' then '0'
						when FieldType = 'real' then '0'
						when FieldType = 'smallint' then '0'
						when FieldType = 'smallmoney' then '0'
						when FieldType = 'tinyint' then '0'
						when FieldType = 'date' then '''20000101'''
						when FieldType = 'datetime' then '''20000101'''
						when FieldType = 'datetime2' then '''20000101'''
						when FieldType = 'datetimeoffset' then '''20000101'''
						when FieldType = 'smalldatetime' then '''20000101'''
						when FieldType = 'time' then '''00:00:00'''
						when FieldType = 'timestamp' then '''20000101'''
						when FieldType = 'binary' then '0x0'
						when FieldType = 'uniqueidentifier' then '0x0'
					else '''''' end) as FieldDefault
			,(case
						when FieldType = 'float' then 'decimal(14,4)'
						when FieldType = 'money' then 'decimal(14,4)'
						when FieldType = 'real' then 'decimal(14,4)'
						when FieldType = 'sql_variant' then 'varbinary(max)'
						when FieldType = 'text' then 'Convertion not allowed' --generate exception!!!
						when FieldType = 'ntext' then 'Convertion not allowed' --generate exception!!!
					else FieldType end) as FieldConvertionType
			FROM @FieldDef 
		)
		,FieldDef2 AS (
			SELECT DISTINCT f.FieldName,f.FieldType,f.FieldIsIdentity,f.FieldIsNullable
				,(case f.FieldIsNullable when 1 then 'isnull(' else ''end) as FieldNamePrefix
				,(case f.FieldIsNullable when 1 then ',' +td.FieldDefault else '' end) as FieldNameSufix
				,f.FieldIsPrimaryKey,f.FieldOrder,f.FieldId, td.FieldConvertionType
			FROM @FieldDef f
			INNER JOIN TypeDafault td on f.FieldType = td.FieldType
		)
		SELECT @FieldList += ',['+f.FieldName+']'
			, @ColumnPairList += '			and '+ f.FieldNamePrefix +'t.['+ f.FieldName+']' +f.FieldNameSufix +'='+ f.FieldNamePrefix +'@'+ replace(replace(f.FieldName,' ',''),'-','')  +f.FieldNameSufix+ CHAR(10)
			, @MergeValuesList += '
		MERGE [lov].[Value] AS target
		USING (SELECT [Action], RowId, '+convert(varchar(10),f.FieldId)+' as FieldId, CONVERT(varbinary(max),['+f.FieldName+']) as FieldValue from @Rows) AS source ([Action], RowId, FieldId, FieldValue)
			ON (target.RowId = source.RowId AND target.FieldId = source.FieldId)
		WHEN MATCHED AND source.[Action]=''UPDATE'' AND source.FieldValue IS NOT NULL THEN
			UPDATE SET FieldValue = source.FieldValue
		WHEN MATCHED AND source.[Action]=''UPDATE'' AND source.FieldValue IS NULL THEN 
			DELETE		
		WHEN NOT MATCHED AND source.FieldValue IS NOT NULL THEN
			INSERT (RowId, FieldId, FieldValue) VALUES (source.RowId, source.FieldId, source.FieldValue);'
			
			,@VariableDeclarationList += ',@'+replace(replace(f.FieldName,' ',''),'-','')+' '+f.FieldType+'=' +td.FieldDefault

			,@PKColumnPairList += (case FieldIsPrimaryKey when 1 then +'			and '+ f.FieldNamePrefix +'t.['+ f.FieldName+']' +f.FieldNameSufix +'='+ f.FieldNamePrefix +'@'+ replace(replace(f.FieldName,' ',''),'-','')  +f.FieldNameSufix+ CHAR(10) else '' end)
			,@FieldDefinitionNoIDList += (case FieldIsIdentity when 0 then + ',['+f.FieldName+'] '+ f.FieldConvertionType else '' end)
			,@VariableAssignmentNoIDList += (case FieldIsIdentity when 0 then + ',@'+replace(replace(f.FieldName,' ',''),'-','')+'=['+f.FieldName+']' else '' end)

			--LightSwitch treats not null views columns as primary keys. Let's pretend that only PK field is not nullable
			--,@FieldDefinitionList += ' ,'+(case f.FieldIsNullable when 0 then 'isnull(' else '' end)+'convert('+f.FieldConvertionType+',['+convert(varchar(10),f.FieldId)+'])'+(case f.FieldIsNullable when 0 then ','+td.FieldDefault+')' else '' end)+' AS ['+f.FieldName+']'
			,@FieldDefinitionList += ' ,'+(case f.FieldIsPrimaryKey when 1 then 'isnull(' else '' end)+'convert('+f.FieldConvertionType+',['+convert(varchar(10),f.FieldId)+'])'+(case f.FieldIsPrimaryKey when 1 then ','+td.FieldDefault+')' else '' end)+' AS ['+f.FieldName+']'
			,@FieldIdList += ' ,['+convert(varchar(10),f.FieldId)+']'
					
		FROM FieldDef2 f
		INNER JOIN TypeDafault td on f.FieldType = td.FieldType
		ORDER BY f.FieldOrder

		SET @FieldList = replace(@FieldList,',,','')
		SET @FieldDefinitionNoIDList	= ''+replace(@FieldDefinitionNoIDList,',,','')
		SET @VariableDeclarationList= ''+replace(@VariableDeclarationList,',,','')

		SET @VariableAssignmentNoIDList	= ''+replace(@VariableAssignmentNoIDList,',,','')
		--SET @MergeValuesList	= ''+replace(@MergeValuesList,',		,','')
		
		SET @ColumnPairList = replace(@ColumnPairList,'and			and','')
		SET @PKColumnPairList = replace(@PKColumnPairList,'and			and','')
		
		SET @FieldDefinitionList = replace(@FieldDefinitionList,', ,',' ')
		SET @FieldIdList = replace(@FieldIdList,', ,',' ')
			
		SET @SQL +=
			  'IF EXISTS (SELECT SCHEMA_NAME([schema_id]), [name] FROM sys.views WHERE Schema_Name([schema_id]) = ''' + @Schema + ''' AND Name = ''' + @EntityName + ''')' + CHAR(10)
			+ '	DROP VIEW [' + @Schema + '].[' + '' + @EntityName + '];' + CHAR(10)
			+ 'GO' + CHAR(10)
			+ '/* ========================================================='  + CHAR(10)
			+ 'Author:		'+SUSER_SNAME() + CHAR(10)
			+ 'Create date: ' + Convert(varchar(10), getdate(),121) + CHAR(10)
			+ 'Description: entity view with multyrecords insert/update/soft delete capabilities.' + CHAR(10)
			+ '			This script is autogenerated. Do not change it directly.' + CHAR(10)
			+ 'Example:'  + CHAR(10)
			+ '	select top 100 * from ['+ @Schema + '].[' + @EntityName +'] t ' + CHAR(10)
			+ '============================================================ */'  + CHAR(10)
			+ 'CREATE VIEW [' + @Schema + '].[' + @EntityName+'] AS' + CHAR(10)
			+ 'SELECT v.*, r.AddDate, r.UpdateDate, r.DeleteDate, r.AddBy, r.UpdateBy, r.DeleteBy' + CHAR(10)
			+ 'FROM (' + CHAR(10)
			+ '	SELECT '+ (CASE @UseRowIdAsPrimaryKey WHEN 1 THEN 'ISNULL' ELSE 'NULLIF' END)+'(RowId, 0x0) as RowId, ' +@FieldDefinitionList + CHAR(10)
			+ '	FROM (' + CHAR(10)
			+ '		SELECT v.RowId, v.FieldId, v.FieldValue' + CHAR(10)
			+ '		FROM [lov].[Value] v' + CHAR(10)
			+ '		INNER JOIN [lov].[Row] r on r.Id = v.RowId' + CHAR(10)
			+ '		WHERE r.[EntityId] = '+convert(varchar(10),@EntityId2) + /*' AND r.DeleteDate is null' +*/ CHAR(10) --do not hide deleted recored from application
			+ '	) AS p' + CHAR(10)
			+ '	PIVOT(Max(FieldValue)' + CHAR(10)
			+ '		FOR FieldId IN('+@FieldIdList+')' + CHAR(10)
			+ '	) AS pvt' + CHAR(10)
			+ ') as v' + CHAR(10)
			+ 'INNER JOIN [lov].[Row] r on r.Id = v.RowId' + CHAR(10)
			+ 'GO' + CHAR(10) 

		--add instead of trigger 
			+ 'IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[' + @Schema + '].[InsteadTrig_' + @EntityName+']''))' + CHAR(10)
			+ '	DROP TRIGGER [' + @Schema + '].[IITrig_' + @EntityName+']' + CHAR(10)
			+ 'GO' + CHAR(10) 
			+ '/* ========================================================='  + CHAR(10)
			+ 'Author:		'+SUSER_SNAME() + CHAR(10)
			+ 'Create date: ' + Convert(varchar(10), getdate(),121) + CHAR(10)
			+ 'Description: the trigger allows to deliver insert/update/soft delete multiple vuew records into physical table' + CHAR(10)
			+ '			This script is autogenerated. Do not change it directly.' + CHAR(10)
			+ 'Example:'  + CHAR(10)
			+ '	delete from ['+ @Schema + '].[' + @EntityName +'] where RowId = ...' + CHAR(10)
			+ '	insert into ['+ @Schema + '].[' + @EntityName +'] values(...)' + CHAR(10)
			+ '	update ['+ @Schema + '].[' + @EntityName +'] where RowId = ...' + CHAR(10)
			+ '============================================================ */'  + CHAR(10)
			+ 'CREATE TRIGGER [' + @Schema + '].[IITrig_' + @EntityName+'] ON [' + @Schema + '].[' + @EntityName+']' + CHAR(10)
			+ 'INSTEAD OF INSERT, UPDATE, DELETE AS'  + CHAR(10)
			+ 'BEGIN' + CHAR(10)
			+ '	SET NOCOUNT ON;' + CHAR(10)
		--soft delete
			+ '	UPDATE r SET r.DeleteDate = GETUTCDATE(), r.DeleteBy = ISNULL(d.DeleteBy, SUSER_SNAME()) FROM [lov].[Row] r INNER JOIN deleted d on r.Id = d.RowId LEFT OUTER JOIN inserted i on r.Id = i.RowId WHERE i.RowId IS NULL' + CHAR(10)
		--insert/update
			+ '	IF EXISTS(SELECT TOP 1 1 FROM inserted)'+ CHAR(10)
			+ '	BEGIN' + CHAR(10)
			+ '		BEGIN TRY' + CHAR(10)
			+ '		BEGIN TRANSACTION' + CHAR(10) + CHAR(10)
		--PRINT '	DECLARE @RowId uniqueidentifier, '+ @VariableDeclarationList
		--if data is deleted only there is no records in inserted table, so we can mark up row as deleted
		--update/insert rows
			+ '		DECLARE @Rows AS TABLE(Action varchar(30), RowId uniqueidentifier, '+@FieldDefinitionNoIDList+');' + CHAR(10)
			+ '		MERGE [lov].[Row] AS target' + CHAR(10)
			+ '		USING inserted AS source ON (target.Id = source.RowId)' + CHAR(10)
			+ '		WHEN MATCHED THEN' + CHAR(10)
			+ '			UPDATE SET UpdateDate = GETUTCDATE(), UpdateBy = ISNULL(source.UpdateBy, SUSER_SNAME())' + CHAR(10)
			+ '		WHEN NOT MATCHED BY TARGET THEN' + CHAR(10)
			+ '			INSERT (EntityId) VALUES ('+convert(varchar(10),@EntityId2) + ')' + CHAR(10)
			+ '		OUTPUT $action as [Action], Inserted.Id as RowId, source.'+Replace(@FieldList,',',',source.')+' INTO @Rows;' + CHAR(10)
			+ @MergeValuesList + CHAR(10) + CHAR(10)
			+ '		COMMIT TRANSACTION;' + CHAR(10)
			+ '		END TRY' + CHAR(10)
			+ '		BEGIN CATCH' + CHAR(10)
	--		+ '			SELECT @error = ERROR_NUMBER(), @errormessage = ERROR_MESSAGE(), @xstate = XACT_STATE();' + CHAR(10)
	--      + '			EXECUTE [dbo].[usp_LogError];' + CHAR(10)
	-- Test whether the transaction is uncommittable.
			+ '			IF (XACT_STATE()) = -1' + CHAR(10)
			+ '			BEGIN' + CHAR(10)
			+ '				Print N''The transaction is in an uncommittable state. Rolling back transaction.''' + CHAR(10)
			+ '				ROLLBACK TRANSACTION;' + CHAR(10)
			+ '			END;' + CHAR(10)
	-- Test whether the transaction is committable.
			+ '			IF (XACT_STATE()) = 1' + CHAR(10)
			+ '			BEGIN' + CHAR(10)
			+ '				Print N''The transaction is committable. Committing transaction.''' + CHAR(10)
			+ '				COMMIT TRANSACTION; ' + CHAR(10)
			+ '			END;' + CHAR(10)
			+ '		END CATCH' + CHAR(10)
			+ '	END' + CHAR(10)
			+ 'END' + CHAR(10)
			+ 'GO' + CHAR(10) 

			FETCH NEXT FROM c_Entities INTO @EntityId2, @Schema, @EntityName, @EntityDescription
		END --if (@@FETCH_STATUS<>-2)
	END -- (@@FETCH_STATUS<>-1)
	CLOSE c_Entities
	DEALLOCATE c_Entities

	IF (@BitMask&1)=1
	BEGIN
		IF LEN(@SQL)<=1024
			Print @SQL
		ELSE
		BEGIN
			--chunking
			SELECT @START_POS = 1, @END_POS = 1
			WHILE @END_POS>0 AND @END_POS<LEN(@SQL)
			BEGIN
				SET @END_POS = CHARINDEX(CHAR(10), @SQL, @START_POS + 1024)
				IF @END_POS=0 SET  @END_POS = LEN(@SQL)
				IF @END_POS>=0
				BEGIN
					Print SUBSTRING(@SQL, @START_POS, @END_POS - @START_POS)
				END
				SET @START_POS = @END_POS+1
			END
		END
	END
	IF (@BitMask&2)=2
	BEGIN
		SELECT @START_POS = 1, @END_POS = 1
		print '--running script...'
		--chunking to avoid GO statement
		WHILE @END_POS>0 AND @END_POS<LEN(@SQL)
		BEGIN
			SET @END_POS = CHARINDEX('GO'+CHAR(10), @SQL, @START_POS)
			IF @END_POS=0 SET @END_POS = LEN(@SQL)
			IF @END_POS>=0 AND @END_POS>@START_POS+3
			BEGIN
				SET @SQL2 = SUBSTRING(@SQL, @START_POS, @END_POS - @START_POS)
				--print @SQL2
				EXEC sp_executesql @SQL2
			END
			SET @START_POS = @END_POS+3
		END
		print '--done!'
	END
END